<?php
class Posts
{
	private static $_instance = null;
	private $_db = null;
	
	private function __construct()
	{
		$this->_db = Yii::app()->db;
		$this->_db->setAttribute(PDO::ATTR_CASE,PDO::CASE_LOWER);
	}
	
	public static function getInstance() 
    {
        if (null === self::$_instance) {
            self::$_instance = new self();
        }
        return self::$_instance;
    }
	
	
	public function getList(array $args = array(), $intPage = 1, $intLimit = MIN_ROWS_PER_PAGE)
	{
		try{
			$defaults = array(
				'site_id' => SITE,
				'taxonomy_id' => 1,
				'post_status' => null,
				'category_id' => null,
				'post_is_sticky' => null		
			);
			$args = array_merge($defaults, $args);
			
			$intStart = ($intPage-1)*$intLimit;
			
			$strCateCond = "";
			if(!empty($args['category_id'])){
				$strCateCond = "INNER JOIN (SELECT post_id
											FROM tbl_cms_post_category
											WHERE category_id IN ( :cates )
								) c ON a.post_id = c.post_id";
			}
			
			$strSql = " 	
						SELECT SQL_CALC_FOUND_ROWS a.* 
						FROM (
								SELECT *
								FROM tbl_cms_posts 
								WHERE taxonomy_id = :taxonomy AND post_status IN (IFNULL(:status, post_status)) AND post_is_sticky = IFNULL(:sticky, post_is_sticky) AND site_id = :site
							 ) a {$strCateCond}
						LIMIT :intStart, :intLimit";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":taxonomy",$args['taxonomy_id'],PDO::PARAM_STR);
			$cmd->bindParam(":status",$args['post_status'],PDO::PARAM_STR);
			$cmd->bindParam(":sticky",$args['post_is_sticky'],PDO::PARAM_STR);
			if(!empty($args['category_id'])) 
				$cmd->bindParam(":cates",$args['category_id'],PDO::PARAM_STR);
			$cmd->bindParam(":site",$args['site_id'],PDO::PARAM_INT);
			$cmd->bindParam(":intStart",$intStart,PDO::PARAM_INT);
			$cmd->bindParam(":intLimit",$intLimit,PDO::PARAM_INT);
			$arrResults = $cmd->queryAll();
			if(!empty($arrResults)){
				//pagination
				$arrResults[0]['total_rows'] = $this->_db->createCommand('SELECT FOUND_ROWS();')->queryScalar();
				//get fields
				foreach($arrResults as $R){
					$arrPostIds[] = $R['post_id'];
				}
				$arrFields = Posts::getInstance()->getFields($arrPostIds);
				//merge fields to list of posts
				foreach($arrResults as $key=>$P){
					$arrResults[$key] = array_merge($arrResults[$key],array('fields'=>isset($arrFields[$P['post_id']])?$arrFields[$P['post_id']]:array()));
				}
			}
			return $arrResults;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function get($id, $site = SITE)
	{
		try{
			$strSql = " SELECT a.*,
							  (SELECT taxonomy_code FROM tbl_cms_taxonomies WHERE taxonomy_id = a.taxonomy_id) as taxonomy_code, 
							  (SELECT GROUP_CONCAT(category_id) FROM tbl_cms_post_category WHERE post_id = a.post_id) as post_category_id,
							  (SELECT GROUP_CONCAT(tag_name) FROM tbl_cms_post_tag INNER JOIN tbl_cms_tags USING (tag_id) WHERE post_id = a.post_id) as post_tags
						FROM tbl_cms_posts a
						WHERE a.post_id=:id AND a.site_id=:site";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$id,PDO::PARAM_INT);
			$cmd->bindParam(":site",$site,PDO::PARAM_INT);
			$arrResults = $cmd->queryRow();
			
			//get fields
			$arrFiels = Posts::getInstance()->getFields($id);
			$arrResults = array_merge($arrResults, array('fields'=>isset($arrFiels[$id])?$arrFiels[$id]:array()));
			
			return $arrResults;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function insert(array $args)
	{		
		try{
			$defaults = array(
				'site_id' => SITE,
				'taxonomy_id' => null,
				'post_code' => null,
				'post_title' => null,
				'post_intro' => null,
				'post_body' => null,
				'post_image' => null,
				'post_allow_search' => 1,
				'post_allow_comment' => 0,
				'post_published_date' => new CDbExpression('NOW()'),
				'post_expiry_date' => null		
			);
			$args = array_merge($defaults, $args);
			
			$cmd = $this->_db->createCommand();
			$cmd->insert('tbl_cms_posts',$args);
			return Yii::app()->db->getLastInsertId();
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function update($id, array $args)
	{		
		try{
			$defaults = array(
				'site_id' => SITE,
				'taxonomy_id' => 2,
				'post_code' => null,
				'post_title' => null,
				'post_intro' => null,
				'post_body' => null,
				'post_image' => null,
				'post_allow_search' => 1,
				'post_allow_comment' => 0,
				'post_published_date' =>  new CDbExpression('NOW()'),
				'post_expiry_date' => null			
			);
			$args = array_merge($defaults, $args);
			
			$cmd = $this->_db->createCommand();
			$cmd->update('tbl_cms_posts',$args, 'post_id=:id AND site_id=:site', array(':id'=>$id, ':site'=>$args['site_id']));
			return 1;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function delete($id){
		try{
			$cmd = $this->_db->createCommand();
			//delete from categories
			$cmd->delete('tbl_cms_post_category', 'post_id=:id', array(':id'=>$id));
			//delete from tags
			$cmd->delete('tbl_cms_post_tag', 'post_id=:id', array(':id'=>$id));
			//delete from fields
			$cmd->delete('tbl_cms_post_field', 'post_id=:id', array(':id'=>$id));
			//delete article
			return $cmd->delete('tbl_cms_posts', 'post_id=:id', array(':id'=>$id));			
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function updateCategory($id, $arr_cates){
		try{
			$arr_cates = !is_array($arr_cates)?explode(',',str_replace(' ', '', $arr_cates)):$arr_cates;
			//delete old categories
			$cmd = $this->_db->createCommand();
			$cmd->delete('tbl_cms_post_category', 'post_id=:id', array(':id'=>$id));
			//insert new categories
			$strSql = 'INSERT INTO tbl_cms_post_category (post_id, category_id) VALUES ';
			$values = array();
			foreach($arr_cates as $cat){
				$values[] = "({$id}, {$cat})";
			}
			$strSql .= implode(',', $values);
			return $this->_db->createCommand($strSql)->execute();
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}	
	}
	
	public function updateTags($id, $tags, $delimiter = ',', $site = SITE){
		try{
			//delete old tags
			$cmd = $this->_db->createCommand();
			$cmd->delete('tbl_cms_post_tag', 'post_id=:id', array(':id'=>$id));
			//update new tag		
			$tags = !is_array($tags)?explode($delimiter, $tags):$tags;
			foreach($tags as $tag){
				//check tag is available?
				$tid = $this->_db->createCommand()->select('tag_id')->from('tbl_cms_tags')->where('tag_name = :tag', array('tag'=>trim($tag)))->queryScalar();
				//if not, insert new tag
				if(!$tid){
					$this->_db->createCommand()->insert('tbl_cms_tags', array('site_id'=>$site, 'tag_name'=>$tag, 'tag_weight'=>0));
					$tid = Yii::app()->db->getLastInsertId();
				}
				//insert tag into posts
				$this->_db->createCommand()->insert('tbl_cms_post_tag', array('post_id'=>$id, 'tag_id'=>$tid));
			}
			return 1;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}	
	}
	
	public function getFields($postId, $fieldId = null)
	{
		if(empty($postId)) throw new Exception('PostId can\'t be null');
		if(is_array($postId)) $postId = implode(',', $postId);
		try{
			$strSQL = " SELECT p.post_id, f.field_name, p.field_value
						FROM (
								SELECT *
								FROM tbl_cms_post_field
								WHERE FIND_IN_SET(post_id, :pid) AND field_id = IFNULL(:fid, field_id)
							 ) AS p INNER JOIN tbl_cms_fields f USING (field_id)
						ORDER BY f.field_position ASC";
			$cmd = $this->_db->createCommand($strSQL);
			$cmd->bindParam(":pid",$postId,PDO::PARAM_STR);
			$cmd->bindParam(":fid",$fieldId,PDO::PARAM_STR);
			$arrResults = $cmd->queryAll();
			//flip to name=>value
			$arrTmp = array();
			foreach($arrResults as $R){
				$arrTmp[$R['post_id']][$R['field_name']] = $R['field_value'];
			}
			return $arrTmp;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}	
	}
	
	public function updateField($postId, $fieldId, $fieldValue)
	{
		if(empty($postId) || empty($fieldId)) throw Exception('Invalid Field Input');
		$args = array(
			'post_id'=>$postId, 
			'field_id'=>$fieldId, 
			'field_value'=>$fieldValue
		);
		try{
			//check if field exits?
			$strSQL = " SELECT 1
						FROM tbl_cms_post_field
						WHERE post_id=:pid AND field_id =:fid";
			$cmd = $this->_db->createCommand($strSQL);
			$cmd->bindParam(":pid",$postId,PDO::PARAM_STR);
			$cmd->bindParam(":fid",$fieldId,PDO::PARAM_STR);
			$found = $cmd->queryScalar();
			
			if($found){
				//update
				return $cmd->update('tbl_cms_post_field',$args, 'post_id=:pid AND field_id=:fid', array(':pid'=>$postId, ':fid'=>$fieldId));
			}
			else{
				//insert
				$this->_db->createCommand()->insert('tbl_cms_post_field', $args);
				return Yii::app()->db->getLastInsertId();
			}
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}	
	}
	
	public function getFieldsByTaxonomy($taxonomyCode)
	{
		if(empty($taxonomyCode)) throw Exception('Taxonomy is null');
		try{
			$strSQL = " SELECT f.*
						FROM tbl_cms_taxonomies as t INNER JOIN tbl_cms_fields as f
						WHERE t.taxonomy_code=:tcode
						ORDER BY f.field_position ASC;";
			$cmd = $this->_db->createCommand($strSQL);
			$cmd->bindParam(":tcode",$taxonomyCode,PDO::PARAM_STR);
			$arrResults = $cmd->queryAll();
			//flip to name=>value
			foreach($arrResults as $R){
				$arrTmp[$R['field_name']] = $R;
			}
			return $arrTmp;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}	
	}
}